/*
 * Copyright (C) 2024 Kevin Buzeau
 *
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program.  If not, see <http://www.gnu.org/licenses/>.
 */
package com.buzbuz.smartautoclicker.core.database.migrations

import androidx.room.ForeignKey
import androidx.room.migration.Migration
import androidx.sqlite.db.SupportSQLiteDatabase

import com.buzbuz.smartautoclicker.core.base.migrations.SQLiteColumn
import com.buzbuz.smartautoclicker.core.base.migrations.SQLiteTable
import com.buzbuz.smartautoclicker.core.base.migrations.copyColumn
import com.buzbuz.smartautoclicker.core.base.migrations.getSQLiteTableReference
import com.buzbuz.smartautoclicker.core.database.ACTION_TABLE
import com.buzbuz.smartautoclicker.core.database.EVENT_TABLE
import com.buzbuz.smartautoclicker.core.database.SCENARIO_TABLE

/**
 * Migration from database v3 to v4.
 *
 * Complete refactoring of the database. Clicks are now Events, and they contains several Actions. This required to
 * change a lot the database. All those sqlite request members are extracted from the schema generated by room.
 *
 * Changes:
 * * click_table is now event_table
 * * conditions no longer have several events/clicks
 * * creation of an action table.
 */
object Migration3to4 : Migration(3, 4) {

    private val scenarioIdForeignKey = SQLiteColumn.ForeignKey(
        name = "scenario_id",
        referencedTable = SCENARIO_TABLE, referencedColumn = "id", deleteAction = ForeignKey.CASCADE,
    )

    private val eventIdForeignKey = SQLiteColumn.ForeignKey(
        name = "eventId",
        referencedTable = EVENT_TABLE, referencedColumn = "id", deleteAction = ForeignKey.CASCADE,
    )

    override fun migrate(db: SupportSQLiteDatabase) {
        // Creates the new tables
        val eventTable = db.createEventTable()
        val actionTable = db.createActionTable()
        val newConditionTable = db.createNewConditionTable()

        // Create indexes tables
        eventTable.createIndex(scenarioIdForeignKey)
        actionTable.createIndex(eventIdForeignKey)
        newConditionTable.createIndex(
            foreignKey = eventIdForeignKey,
            indexName = "index_condition_table_eventId"
        )

        // Migrate from old format
        eventTable.insertEvents()
        newConditionTable.insertConditions()
        actionTable.insertClickActions()
        actionTable.insertSwipeActions()
        actionTable.insertPauseActions()

        // Delete old tables
        db.getSQLiteTableReference("click_table").dropTable()
        db.getSQLiteTableReference("condition_table").dropTable()
        db.getSQLiteTableReference("ClickConditionCrossRef").dropTable()

        // Rename condition_table_new in condition_table to finish the replacement
        newConditionTable.alterTableRename("condition_table")
    }

    /** Create the event table, replacement of the click table. */
    private fun SupportSQLiteDatabase.createEventTable(): SQLiteTable =
        getSQLiteTableReference(EVENT_TABLE).apply {
            createTable(
                columns = setOf(
                    scenarioIdForeignKey,
                    SQLiteColumn.Text("name"),
                    SQLiteColumn.Int("operator"),
                    SQLiteColumn.Int("priority"),
                    SQLiteColumn.Int("stop_after", isNotNull = false),
                ),
            )
        }

    /** Create the new action table. */
    private fun SupportSQLiteDatabase.createActionTable(): SQLiteTable =
        getSQLiteTableReference(ACTION_TABLE).apply {
            createTable(
                columns = setOf(
                    eventIdForeignKey,
                    SQLiteColumn.Int("priority"),
                    SQLiteColumn.Text("name"),
                    SQLiteColumn.Text("type"),
                    SQLiteColumn.Int("x", isNotNull = false),
                    SQLiteColumn.Int("y", isNotNull = false),
                    SQLiteColumn.Long("pressDuration", isNotNull = false),
                    SQLiteColumn.Int("fromX", isNotNull = false),
                    SQLiteColumn.Int("fromY", isNotNull = false),
                    SQLiteColumn.Int("toX", isNotNull = false),
                    SQLiteColumn.Int("toY", isNotNull = false),
                    SQLiteColumn.Long("swipeDuration", isNotNull = false),
                    SQLiteColumn.Long("pauseDuration", isNotNull = false),
                ),
            )
        }

    /**
     * Creates the condition table. As the changes on the old condition table are too big, we create a new one here
     * to copy all the values from the old one before renaming this one.
     */
    private fun SupportSQLiteDatabase.createNewConditionTable(): SQLiteTable =
        getSQLiteTableReference("condition_table_new").apply {
            createTable(
                columns = setOf(
                    eventIdForeignKey,
                    SQLiteColumn.Text("path"),
                    SQLiteColumn.Int("area_left"),
                    SQLiteColumn.Int("area_top"),
                    SQLiteColumn.Int("area_right"),
                    SQLiteColumn.Int("area_bottom"),
                    SQLiteColumn.Int("threshold", defaultValue = "1"),
                ),
            )
        }
}


/** Insert an event for each old click. */
private fun SQLiteTable.insertEvents() =
    insertIntoSelect(
        fromTableName = "click_table",
        columnsToFromColumns = arrayOf(
            "id" to "clickId",
            "scenario_id" to "scenario_id",
            "name" to "name",
            "operator" to "operator",
            "priority" to "priority",
            "stop_after" to "stop_after",
        )
    )

/** Insert an action for each old click. */
private fun SQLiteTable.insertClickActions() =
    insertIntoSelect(
        fromTableName = "click_table",
        extraClause = "WHERE click_table.type = 1",
        columnsToFromColumns = arrayOf(
            "eventId" to "clickId",
            "priority" to "0",
            "name" to "name",
            "type" to "\"CLICK\"",
            "x" to "from_x",
            "y" to "from_y",
            "pressDuration" to "1",
        )
    )

/** Insert an action for each old swipe. */
private fun SQLiteTable.insertSwipeActions() =
    insertIntoSelect(
        fromTableName = "click_table",
        extraClause = "WHERE click_table.type = 2",
        columnsToFromColumns = arrayOf(
            "eventId" to "clickId",
            "priority" to "0",
            "name" to "name",
            "type" to "\"SWIPE\"",
            "fromX" to "from_x",
            "fromY" to "from_y",
            "toX" to "to_x",
            "toY" to "to_y",
            "swipeDuration" to "175",
        )
    )

/** Insert a pause action for each old action we had to replace the delay_after. */
private fun SQLiteTable.insertPauseActions() =
    insertIntoSelect(
        fromTableName = "click_table",
        columnsToFromColumns = arrayOf(
            "eventId" to "clickId",
            "priority" to "1",
            "name" to "\"Pause\"",
            "type" to "\"PAUSE\"",
            "pauseDuration" to "delay_after",
        )
    )

/** Copy the existing conditions into the new table. */
private fun SQLiteTable.insertConditions() =
    insertIntoSelect(
        fromTableName = "condition_table",
        extraClause = "INNER JOIN ClickConditionCrossRef on ClickConditionCrossRef.path = condition_table.path",
        columnsToFromColumns = arrayOf(
            "eventId" to "ClickConditionCrossRef.clickId",
            "path" to "ClickConditionCrossRef.path",
            copyColumn("area_left"),
            copyColumn("area_top"),
            copyColumn("area_right"),
            copyColumn("area_bottom"),
            copyColumn("threshold"),
        )
    )